2014-02-14 - 11379.100 - Spec - EPM #CrystalReportDevelopment
SPECIFICATIONS
11379.100 - EPM
Requirement Summary
Need to update the EPM reports with new budget data.
Admin Info
Purpose
|
Need to update the EPM reports with new budget data.
|
Requested By
|
Mark Harris
|
Spec Created By
|
Praveen Guntuka
|
Spec Created Date
|
02/14/2014
|
Spec QA by
|
Uday Kumar P
|
Objects
|
EPM Reports
|
Document Status
|
Complete
|
References
Prior Tickets
9760.300
Documents
EPM BPC quries..txt
EPM_Formulas_S_SO.txt
EPM Formulas.xlsx
Changes.pdf
Functional Requirement
1. Need to design the following EPM Reports on BPC queries as per NEC Requirements:
- EPM200 - Customer Scorecard.
- EPM210 - Customer Segment Scorecard.
- EPM220 - License Scorecard.
- EPM230 - Product Mix Scorecard.
- EPM240 - Silhouette Scorecard.
- EPM250 - Monthly Scorecard.
2. Need to remove all the budget files and replace with budget fields from new queries based on ZBPC_MP multiprovider.
3. Need to fix the year budget value at the top of the report.
4. Need to remove the parameters for all the sub reports and hardcode the values for ‘TOP N’ except in License subreport.
5. Need to set the ‘TOP N’ for product mix, silhouette, customer subreports and all values for customer segment & license subreports.
6. Need to display all fields that have budget values and move the other licenses without budget into ‘Others’ category for all Subreports except Customer Segment subreport.
7. Need to move Other field to 'Others' Category.
8. Need to leave customer segment subreport as it is without any changes.
For Main Report YTD Section:
Show four sections as seen below:
- Year to date(YTD) section.
- Total Year section.
- Contribution % section.
- Quantity section.
1. YTD Section includes:
- Shipped, Last Year, Budget, LY Variance and Budget Variance for YTD.
- Coverage Percentage for Last Year and Budget for YTD.
- Shipped, Last Year, Budget as direct fields from BEx query.
- LY Variance = Shipped YTD - Last Year YTD.
- Budget Variance = Shipped YTD - Budget YTD.
- Coverage % for Last Year YTD = (Shipped YTD / Last Year YTD)*100.
- Coverage % for Budget YTD = (Shipped YTD / Budget YTD)*100.
2. Total Year Section includes:
- Open, Total, Last Year and Budget showing full year values.
- Coverage Percentage for Last Year and Budget for Full Year.
- All fields as direct fields from BEx query.
- Coverage % for Last Year FY = (Total TY / Total Shipped LY)*100.
- Coverage % for Budget FY = (Total TY / Budget TY)*100.
3. Contribution % Section includes:
- TY, LY and Budget Contribution percentages for YTD and full year.
- For YTD column:
TY = Shipped Contribution YTD / Shipped Revenue.
LY = Shipped Contribution Last Yr YTD / Shipped Revenue Last Yr YTD.
Budget = Budget Contribution YTD / Budget Revenue.
TY = (Shipped + Open order Contribution this year) / (Shipped + Open Revenue this year).
LY = Shipped Contribution Last Yr / Shipped Revenue Last Yr.
Budget = Budget Contribution / Budget Revenue.
4. Quantity Section includes:
- This Year(TY), Last Year(LY) and Budget quantities.
- All the fields as direct fields from BEx Query.
Grouping for each report:
- EPM199 grouping should be on Region.
- EPM200 grouping should be Region > Customer > Sales Org.
- EPM210 grouping should be Region > Customer Group 9 > Sales Org.
- EPM220 grouping should be Region > License > Sales Org.
- EPM230 grouping should be Region > Material Group > Sales Org.
- EPM240 grouping should be Region > Silhouette > Sales Org.
- EPM250 grouping should be on Region.
SubReports Section:
1. Each subreport should have three sections namely:
- Revenue.
- Contribution %.
- Quantity.
2. Each subreport need to show Shipped, Open, Total, Last Year, Budget, Last Year Variance and Budget Variance in revenue section.
3. Each subreport need to show This Year, Last Year and Budget percentages in Contribution % section.
4. Each subreport need to show This Year, Last Year and Budget quantities in Quantity section.
5. Formulas used in Revenue section:
- LY Variance = Shipped Revenue YTD - Last Year Revenue YTD.
- Budget Variance = Shipped Revenue YTD - Budget Revenue YTD.
6. Formulas in Contribution % Section:
- TY = Shipped Contribution YTD / Shipped Revenue YTD.
- LY = Shipped Contribution Previous YTD / Shipped Revenue Previous YTD.
- Budget = Budget Contribution YTD / Budget Revenue YTD.
7. Each subreport need to sort based on Shipped Revenue YTD except Customer Segment subreport that has to sort based on Customer Segment ID in descending order.
SubReports:
1. For Product Mix Subreport :
- Need to show all Product Mix(Material Group) values in the subreport.
- Grouping should be based on main report grouping order.
- Need to maintain last group level based on Material Group field.
2. For License Subreport :
- Grouping should be based on main report grouping order.
- Need to maintain last group level based on License field.
- In Others section category, need to group the Licenses which are having Budget value as zero and Other Licenses.
3. For Customer Segment Subreport :
- Need to sort the Customer segments based on Customer Segment ID.
- Need to show all Customer segment values in the subreport.
- Grouping should be based on main report grouping order.
- In each Group, need to maintain last group level based on Customer Group 9 field.
4. For Customer Subreport :
- Grouping should be based on main report grouping order.
- In each Group, need to maintain last group level based on Customer field.
- In Others category, need to group the Customers between 9100001 to 9100009.
5. For Silhouette Subreport :
- Grouping should be based on main report grouping order.
- In each Group, need to maintain last group level based on Silhouette field.
- In Others category, need to group the Silhouettes which are having budget value as zero and Other Silhouettes.
For MTD Section Subreports:
- In MTD section, subreport should have three sections namely MTD, Contribution % and Quantity section.
- MTD section need to show Shipped, Last Year, Budget, LY Variance and Budget variance in MTD.
- Contribution section need to show TY, LY and Budget percentages for MTD.
- Quantity section need to show TY, LY and Budget quantities for MTD.
All subreports in MTD section need to show formatting and formulas as above section but values should be in MTD.
Further the MTD should display the following subreports as specified below:
- EPM199 should show Product Mix, License, Customer Segment, Customer and Silhouette subreports.
- EPM200 should show Product Mix, Customer Segment, License and Silhouette subreports.
- EPM210 should show Product Mix, License, Customer and Silhouette subreports.
- EPM220 should show Product Mix, Customer Segment, Customer and Silhouette subreports.
- EPM230 should show License, Customer Segment, Customer and Silhouette subreports.
- EPM240 should show License, Customer Segment and Customer subreports
Call with Ashwin on 04/28/2014: New Requirement:
- Need to restrict the Customer subreport Others section only between "9100001" to "9100009" and not based on Budget values.
- Need to restrict the Silhouette subreport Others section for Budget equal to zero at respective group level and Silhouette name equal to Others.
Note: Collection subreport is not present In EPM199, EPM200, EPM210, EPM220, EPM230, EPM240 reports now as we do not have budget values for Collection. Once those values are maintained in future, then we need to implement the same for Collection subreport and need to show collection values in appropriate EPM reports.
Solution Summary
These reports are need to be designed using new queries based on ZBPC_MP multiprovider. Listed all the EPM BPC BEx queries in attached file.
Test Plan
High level validation done by BMS.
Detailed level validation done by NEC.
Solution Details
These reports need to be designed using SAP BEx queries to fetch Shipped Revenue, Open Revenue, Total Revenue, Last Year Revenue, Budget Revenue, LY Variance Revenue, Budget Variance Revenue, TY Contribution, LY Contribution, Budget Contribution, TY Quantity, LY Quantity and Budget Quantity.
Issues
["9100001" to "9100009"]
None